package qlttn.dao.daointerface;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import qlttn.common.utility.ConnectionDBUtility;
import qlttn.common.utility.LoggerUtility;
import qlttn.common.valueobjects.UserVO;
import qlttn.web.bean.UserBean;

public class UserDAOImpl implements UserDAO {

	/**
	 * Look up user in the database.
	 * 
	 * @param userBean
	 * @return UserBean
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */
	@Override
	public UserVO getUserByPassword(UserVO userVO) throws SQLException,
			ClassNotFoundException {
		LoggerUtility
				.logger("UserDAOImpl.getUserByPassword()..............start");
		UserVO user = null;
		Connection con = null;
		PreparedStatement pre = null;
		ResultSet rs = null;
		try {
			con = ConnectDAO.getConnect();
			String sql = "SELECT * FROM UserInfo WHERE Username=? AND Password= ?";
			pre = con.prepareStatement(sql);
			pre.setString(1, userVO.getUserName());
			pre.setString(2, userVO.getPassword());
			rs = pre.executeQuery();
			// Return new UserBean if userName and password have exited in the
			// database.
			if (rs.next()) {
				user = new UserVO();
			}
		} finally {
			ConnectionDBUtility.closeConnection(con, pre, rs);
		}
		LoggerUtility
				.logger("UserDAOImpl.getUserByPassword()..............end");
		return user;
	}

	@Override
	public UserVO getUserInfo(String userName) throws SQLException,
			ClassNotFoundException {
		LoggerUtility.logger("UserDAOImpl.getUserInfo()..............start");
		UserVO userVO = null;
		Connection con = null;
		PreparedStatement pre = null;
		ResultSet rs = null;
		try {
			con = ConnectDAO.getConnect();
			String sql = "SELECT * FROM UserInfo WHERE Username=?";
			pre = con.prepareStatement(sql);
			pre.setString(1, userName);
			rs = pre.executeQuery();
			// Return new UserBean if userName and password have exited in the
			// database.
			if (rs.next()) {
				userVO = new UserVO();
				userVO.setUserName(rs.getString(1));
				userVO.setPassword(rs.getString(2));
				userVO.setRole(String.valueOf(rs.getInt(3)));
				userVO.setFirstName(rs.getString(4));
				userVO.setLastName(rs.getString(5));
				userVO.setEmail(rs.getString(6));
			}
		} finally {
			ConnectionDBUtility.closeConnection(con, pre, rs);
		}
		LoggerUtility.logger("UserDAOImpl.getUserInfo()..............end");
		return userVO;
	}

	@Override
	public boolean addUser(UserBean userBean) throws SQLException,
			ClassNotFoundException {
		LoggerUtility.logger("UserDAOImpl.addUser()..............start");
		Connection con = null;
		PreparedStatement pre = null;
		ResultSet rs = null;
		try {
			con = ConnectDAO.getConnect();
			String sql = "";
			pre = con.prepareStatement(sql);

		} finally {
			ConnectionDBUtility.closeConnection(con, pre, rs);
		}
		LoggerUtility.logger("UserDAOImpl.addUser()..............end");
		return false;
	}

	public ArrayList<UserVO> getListUsers(String role) throws SQLException,
			ClassNotFoundException {
		LoggerUtility.logger("UserDAOImpl.getListUsers()..............start");
		ArrayList<UserVO> listUsers = new ArrayList<UserVO>();
		UserVO userVO = null;
		Connection con = null;
		PreparedStatement pre = null;
		ResultSet rs = null;
		try {
			con = ConnectDAO.getConnect();
			String sql = "SELECT * FROM UserInfo";
			pre = con.prepareStatement(sql);
			//pre.setString(1, role);
			rs = pre.executeQuery();
			// Get UserVO and store in the ArrayList<UserVO>.
			while (rs.next()) {
				userVO = new UserVO();
				userVO.setUserName(rs.getString(1));
				userVO.setPassword(rs.getString(2));
                userVO.setRole(rs.getString(3));
                userVO.setFirstName(rs.getString(4));
                userVO.setLastName(rs.getString(5));
                userVO.setEmail(rs.getString(6));
				listUsers.add(userVO);
			}

		} finally {
			ConnectionDBUtility.closeConnection(con, pre, rs);
		}
		LoggerUtility.logger("UserDAOImpl.getListUsers()..............end");
		return listUsers;
	}

}
